package com.tjh2.preparedstatement.crud;

import com.tjh2.util.JDBCUtils;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;

/**
 * @author tang'jia'hao
 * @description :使用preparedStatement来替换statement,实现对数据表的增删改查的操作
 * 增删改查
 * @package com.tjh2.preparedstatement.crud(jdbc_1)
 * @date 2021-09-13 20:52
 */
public class PreparedStatementUpdateTest {

    @Test
    public void testCommon() {
//        String sql = "delete from customers where id = ?";
//        update(sql, 3);

        String sql = "update `order` set order_name = ? where order_id = ?";
        update(sql, "DD", 2);
    }

    // 通用的增删改操作
    public void update(String sql, Object ...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0; i < args.length; i++){
                ps.setObject(i+1, args[i]);
            }
            ps.execute();
        } catch (IOException | ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
    }

    // 测试修改customers表的一条记录
    @Test
    public void testUpdate() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 获取数据库连接
            conn = JDBCUtils.getConnection();
            // 预编译sql语句,返回preparedStatement实例
            String sql = "update customers set name = ? where id = ?";
            ps = conn.prepareStatement(sql);
            // 填充占位符
            ps.setObject(1, "莫扎特");
            ps.setObject(2, 18);
            // 执行
            ps.execute();
        } catch (IOException | ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 资源的    关闭
            JDBCUtils.closeResource(conn, ps);
        }
    }

    @Test
    public void testInsert() throws IOException, ClassNotFoundException, SQLException, ParseException {
        //        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        Class.forName(driverClass);

        // 获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println("conn = " + conn);

        // 预编译sql语句，返回preparedStatement实例
        String sql = "insert into customers(name,email,birth)values(?,?,?)"; // 问号是占位符
        PreparedStatement ps = conn.prepareStatement(sql);

        // 填充占位符
        ps.setString(1, "张三");
        ps.setString(2, "12345@qq.com");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date date = sdf.parse("1000-01-01");
        ps.setString(3, String.valueOf(new Date(date.getTime())));

        ps.execute();
        ps.close();
    }
}
